﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Runtime.CompilerServices;

//[assembly: InternalsVisibleTo("TST_ALL")]
using Interfaces.CommonData;

namespace StockInfoDataLayer
{
    public class DBManager
    {
        // get connection string from App settings
        private static string _ConnectionString; //= @"Data Source=BIGBOY\HOME; Integrated Security=SSPI;";
        private static string _initialCatalog;
        private static DBManager _dbManager;
        

        private DBManager()
        {}

        public DBStatus EvaluateDBStatus()
        {
            SqlConnection conn;
            // 1. try to connect
            try
            {
                conn = new SqlConnection(_ConnectionString);
                conn.Open();
                conn.Close();
            }
            catch (Exception)
            {
                return DBStatus.ServerDisconnect;
            }
            // 2. Check db existence
            try
            {
                conn = new SqlConnection(_ConnectionString + string.Format(";Initial Catalog={0};", _initialCatalog));
                conn.Open();
                conn.Close();
            }
            catch (Exception)
            {
                return DBStatus.DBNotCreated;
            }
            // 3. Check data existence
            string sqlCounts = @"Select 'PriceTable', COUNT(*) c from PriceTable
                                 Union Select 'StockTable', COUNT(*) c from StockTable";
            DBStatus status = DBStatus.Populated;

            using (conn = new SqlConnection(_ConnectionString + string.Format(";Initial Catalog={0};", _initialCatalog)))
            {
                conn.Open();
                SqlCommand cmdCount = new SqlCommand();
                cmdCount.Connection = conn;
                cmdCount.CommandText = sqlCounts;
                var rd = cmdCount.ExecuteReader();
                while (rd.Read())
                {
                    if ((int) rd[1] == 0)
                    {  // at least one table is empty
                        status = DBStatus.DBNotPopulated;
                        break;
                    }
                }
                rd.Close();
                conn.Close();
            }

            return status;
        }

        public static DBManager CreateInstance(string connStr, string initialCatalog)
        {
            if(_dbManager == null)
            { 
                _dbManager = new DBManager();
                _ConnectionString = connStr;
                _initialCatalog = initialCatalog;
            }

            return _dbManager;
        }
        public static DBManager GetInstance()
        {
            if (_dbManager == null)
            {
                throw new Exception("DBManager singleton must be created and initiated by using CreateInstance call.");
            }

            return _dbManager;
        }



        public SqlConnection CreateConnection()
        {
            SqlConnection conn = new SqlConnection(_ConnectionString + string.Format(";Initial Catalog={0};", _initialCatalog));
            return conn;
        }

        public void CreateDataBase()
        {
            // TODO: add primary and foreign keys
            string createScript = @"----------------------------------------------------
                                    USE master;
                                    GO
                                    IF DB_ID (N'{0}') IS NOT NULL
                                    DROP DATABASE {0};
                                    GO
                                    CREATE DATABASE {0};
                                    GO
                                    USE {0};
                                    GO
                                    CREATE TABLE StockTable
                                    (
	                                    ID INT
                                    ,	Ticker VARCHAR(10)
                                    ,	[Company name] VARCHAR(100)
                                    )
                                    GO
                                    CREATE TABLE PriceTable
                                    (
	                                    StockID INT NOT NULL
                                    ,	[Date] DATETIME NOT NULL
                                    ,	[Adjusted Closing Price] NUMERIC(18,2) NULL
                                    )
                                    GO";
            createScript = string.Format(createScript, _initialCatalog);
            RunNonExecWithBatches(createScript);
        }

        public void DropDataBase()
        {
            string dropScript = @"----------------------------------------------------
                                    USE master;
                                    GO
                                    IF DB_ID (N'{0}') IS NOT NULL
                                    DROP DATABASE {0};
                                    GO";
            dropScript = string.Format(dropScript, _initialCatalog);

            System.Data.SqlClient.SqlConnection.ClearAllPools();
            RunNonExecWithBatches(dropScript);

        }

        private bool RunNonExecWithBatches(string script)
        {
            using (var conn = new SqlConnection(_ConnectionString))
            {
                conn.Open();
                SqlCommand cmdCreateDB = new SqlCommand();
                cmdCreateDB.Connection = conn;
                foreach (var sqlBatch in script.Split(new[] { "GO" }, StringSplitOptions.RemoveEmptyEntries))
                {
                    cmdCreateDB.CommandText = sqlBatch;
                    cmdCreateDB.ExecuteNonQuery();
                }
                conn.Close();
            }
            //TODO: process errors
            return true;
        }

    }
}
